package com.zfh.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zfh.entity.User;
import com.zfh.util.DatabaseConnection;

public class UserDAOImpl2 implements UserDAO {
	
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	// 连库四大参
	private static final String driverClassName = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/curdTestDB?characterEncoding=utf8&useSSL=false";
	private static final String username = "root";
	private static final String password = "root"; 

	// sql
	private String sql = null;
	@Override
	public List<User> getAll() throws Exception {
		sql = "select id,name,gender,bir,score from t_user  order by id desc";
		Class.forName(driverClassName);
		conn = DriverManager.getConnection(url, username, password);
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		List<User> users = new ArrayList<User>();
		while (rs.next()) {
			User user = new User();
			user.setId(rs.getString(1));
			user.setName(rs.getString(2));
			user.setGender(rs.getString(3));
			user.setBir(rs.getDate(4));
			user.setScore(rs.getInt(5));
			users.add(user);
		}
		if(rs!=null)rs.close();
		if(ps!=null)ps.close();
		if(conn!=null)conn.close();
		return users;
	}

	@Override
	public User getOne(String id) throws Exception {
		sql = "select id,name,gender,bir,score from t_user where id=?";
		Class.forName(driverClassName);
		conn = DriverManager.getConnection(url, username, password);
		ps = conn.prepareStatement(sql);
		ps.setObject(1, id);
		rs = ps.executeQuery();
		User user = null;
		if (rs.next()) {
			user = new User();
			user.setId(rs.getString(1));
			user.setName(rs.getString(2));
			user.setGender(rs.getString(3));
			user.setBir(rs.getDate(4));
			user.setScore(rs.getInt(5));
		}
		if(rs!=null)rs.close();
		if(ps!=null)ps.close();
		if(conn!=null)conn.close();
		return user;
	}

	@Override
	public Integer getCount() throws Exception {
		sql = "select count(id) from t_user";
		Class.forName(driverClassName);
		conn = DriverManager.getConnection(url, username, password);
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		int count = 0;
		if (rs.next()) {
			count = rs.getInt(1);
		}
		if(rs!=null)rs.close();
		if(ps!=null)ps.close();
		if(conn!=null)conn.close();
		return count;
	}

	@Override
	public void insertUser(User user) throws Exception {
		// sql
		sql = "insert into t_user values(?,?,?,?,?)";

		Class.forName(driverClassName);
		conn = DriverManager.getConnection(url, username, password);
		ps = conn.prepareStatement(sql);
		ps.setObject(1, user.getId());
		ps.setObject(2, user.getName());
		ps.setObject(3, user.getGender());
		ps.setObject(4, user.getBir());
		ps.setObject(5, user.getScore());
		ps.executeUpdate();
		if(ps!=null) ps.close();
		if(conn!=null) conn.close();
	}

	@Override
	public void updateUser(User user) throws Exception {
		// sql
		sql = "update t_user set name=?,gender=?,bir=?,score=? where id = ?";

		Class.forName(driverClassName);
		conn = DriverManager.getConnection(url, username, password);
		ps = conn.prepareStatement(sql);
		ps.setObject(1, user.getName());
		ps.setObject(2, user.getGender());
		ps.setObject(3, user.getBir());
		ps.setObject(4, user.getScore());
		ps.setObject(5, user.getId());
		ps.executeUpdate();
		if(ps!=null) ps.close();
		if(conn!=null) conn.close();
		
	}

	@Override
	public void deleteUser(String id) throws Exception {
		// sql
		sql = "delete from t_user where id=?";

		Class.forName(driverClassName);
		conn = DriverManager.getConnection(url, username, password);
		ps = conn.prepareStatement(sql);
		ps.setObject(1, id);
		ps.executeUpdate();
		if(ps!=null) ps.close();
		if(conn!=null) conn.close();
		
	}

}
